﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml.Linq;

public partial class PledgeCountXml : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        int pageSize = 10;
        if (!string.IsNullOrEmpty(Request.QueryString["pageSize"]))
        {
            try
            {
                pageSize = int.Parse(Request.QueryString["pageSize"]);
            }
            catch (FormatException) { }
        }

        int pageIndex = 0;
        if (!string.IsNullOrEmpty(Request.QueryString["pageIndex"]))
        {
            try
            {
                pageIndex = int.Parse(Request.QueryString["pageIndex"]);
            }
            catch (FormatException) { }
        }

        int decimalPlaces = 2;
        if (!string.IsNullOrEmpty(Request.QueryString["decimalPlaces"]))
        {
            try
            {
                decimalPlaces = int.Parse(Request.QueryString["decimalPlaces"]);
            }
            catch (FormatException) { }
        }

        string format = string.Concat("0.", string.Join(null, new int[decimalPlaces].Select(x => "#").ToArray()));

        XDocument doc = new XDocument(
          new XDeclaration("1.0", "UTF-8", "yes"),
          new XElement("schools")
        );


        //var context = new ClimateChangeChampionsData.DataContext();
        var context = new ClimateChangeChampionsData.DataContext("Data Source=88.208.201.205;Initial Catalog=ClimateChangeChampionsV2;User ID=sa;Password=Listman5*");
        //var total = context.Pledges.Where(x => x.Authenticated).Count();

        var tmpSchoolIds = context.Pledges.Where(x => x.Authenticated).Join(context.Users, x => x.UserId, y => y.Id, (x, y) => y.Id);
        var tmpData1 = tmpSchoolIds.Join(context.UserCustomProperties.Where(x => x.Name == "School Name"), x => x, y => y.UserId, (x, y) => new { SchoolId = x, Name = y.Value });
        var tmpData2 = tmpData1.Join(context.CarbonCalculations.Where(x => x.Audit == 1), x => x.SchoolId, y => y.UserId, (x, y) => new { SchoolId = x.SchoolId, Name = x.Name, Pupils = y.NoPupils });
        var grouped = tmpData2.GroupBy(x => x.SchoolId).Select(x => new { Name = x.First().Name, Count = x.Count(), Pupils = x.First().Pupils, PledgesPerPupils = (float)x.Count() / (float)x.First().Pupils }).OrderByDescending(x => x.PledgesPerPupils).ThenByDescending(x => x.Count);

        var nodes = grouped.Skip(pageIndex * pageSize).Take(pageSize).ToList().Select(x => new XElement("school", new XElement("schoolName", x.Name), new XElement("schoolTotal", x.PledgesPerPupils.ToString(format)), new XElement("pledges", x.Count.ToString()), new XElement("pupils", x.Pupils.ToString())));

        doc.Root.Add(new XAttribute("created", DateTime.Now.ToString("f")));
        //doc.Root.Add(new XAttribute("totalPledges", total.ToString()));
        doc.Root.Add(new XAttribute("totalSchools", grouped.Count().ToString()));
        doc.Root.Add(new XAttribute("pageIndex", pageIndex));
        doc.Root.Add(new XAttribute("pageSize", pageSize));
        doc.Root.Add(nodes);

        Response.Clear();
        Response.ContentType = "text/xml";
        System.Xml.XmlTextWriter writer = new System.Xml.XmlTextWriter(Response.OutputStream, System.Text.Encoding.UTF8);
        doc.WriteTo(writer);
        writer.Close();
        Response.End();
    }
}
